﻿using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using System.Web.Security;

public partial class AUVDGroups : System.Web.UI.Page
{
    MySqlDataAdapter da;
    DataSet ds = new DataSet();
    MySqlCommand cmd = new MySqlCommand();
    MySqlConnection con;

    //private static string connectionString = "server=localhost;User Id=root;Password=root;database=workorganizer";
    static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["LoginName"] == null || Session["UserId"] == null)
        {
            Response.Redirect("Home.aspx");
        }
        else
        {
            if (Session["UserTypeId"] != null && Convert.ToInt32(Session["UserTypeId"]) == 1)
            {
                if (!IsPostBack)
                {
                    BindGroupData();
                    BindUserData();
                }
            }
            else
            {
                FormsAuthentication.SignOut();
                Session["LoginName"] = "";
                Session["UserType"] = "";
                Session["MobileNumber"] = "";
                Session["EmailId"] = "";
                Response.Redirect("Home.aspx");
            }
        }
    }

    public void BindGroupData()
    {
        con = new MySqlConnection(connectionString);
        cmd.CommandText = "Select * from UserTypes";
        cmd.Connection = con;
        da = null;
        ds = new DataSet();
        da = new MySqlDataAdapter(cmd);
        da.Fill(ds);
        con.Open();
        cmd.ExecuteNonQuery();
        Grid.DataSource = ds;
        Grid.DataBind();
        con.Close();
    }


    public void BindUserData()
    {
        con = new MySqlConnection(connectionString);
        cmd.CommandText = "select u.UserId,ut.UserType,u.LoginName,u.MobileNumber,u.EmailId,u.CreatedDate from users u LEFT JOIN UserTypes ut ON u.UserTypeId = ut.UserTypeId";
        cmd.Connection = con;
        da = null;
        ds = new DataSet();
        da = new MySqlDataAdapter(cmd);
        da.Fill(ds);
        con.Open();
        cmd.ExecuteNonQuery();
        UserGrid.DataSource = ds;
        UserGrid.DataBind();
        con.Close();
    }

    #region Group
    protected void Grid_EditCommand(object source, DataGridCommandEventArgs e)
    {
        Grid.EditItemIndex = e.Item.ItemIndex;
        BindGroupData();
    }
    protected void Grid_CancelCommand(object source, DataGridCommandEventArgs e)
    {
        Grid.EditItemIndex = -1;
        BindGroupData();
    }
    protected void Grid_DeleteCommand(object source, DataGridCommandEventArgs e)
    {
        con = new MySqlConnection(connectionString);
        cmd.Connection = con;
        int UserTypeId = Convert.ToInt32(Grid.DataKeys[(int)e.Item.ItemIndex]);
        cmd.CommandText = "Delete from UserTypes where UserTypeId=" + UserTypeId;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        Grid.EditItemIndex = -1;
        BindGroupData();
    }
    protected void Grid_UpdateCommand(object source, DataGridCommandEventArgs e)
    {
        UserType u = new UserType();
        u.createdDate = DateTime.Now;

        con = new MySqlConnection(connectionString);
        int UserTypeId = Convert.ToInt32(Grid.DataKeys[(int)e.Item.ItemIndex]);
        //cmd.Parameters.AddWithValue("@UserTypeId", SqlDbType.Int).Value = e.Item.Cells[0].Text;
        cmd.Parameters.AddWithValue("@UserType", ((TextBox)e.Item.Cells[1].Controls[0]).Text);
        cmd.Parameters.AddWithValue("@CreatedDate", u.createdDate);
        cmd.CommandText = "Update UserTypes set UserType=@UserType,CreatedDate=@CreatedDate where UserTypeId=" + UserTypeId;
        cmd.Connection = con;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        Grid.EditItemIndex = -1;
        BindGroupData();
    }
    #endregion

    #region User group
    protected void UserGrid_EditCommand(object source, DataGridCommandEventArgs e)
    {
        UserGrid.EditItemIndex = e.Item.ItemIndex;
        BindUserData();
    }
    protected void UserGrid_CancelCommand(object source, DataGridCommandEventArgs e)
    {
        UserGrid.EditItemIndex = -1;
        BindUserData();
    }
    protected void UserGrid_DeleteCommand(object source, DataGridCommandEventArgs e)
    {
        con = new MySqlConnection(connectionString);
        cmd.Connection = con;
        int UserId = Convert.ToInt32(UserGrid.DataKeys[(int)e.Item.ItemIndex]);
        cmd.CommandText = "Delete from Users where UserId=" + UserId;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        UserGrid.EditItemIndex = -1;
        BindUserData();
    }
    protected void UserGrid_UpdateCommand(object source, DataGridCommandEventArgs e)
    {
        UserType u = new UserType();
        u.createdDate = DateTime.Now;

        con = new MySqlConnection(connectionString);
        int UserId = Convert.ToInt32(UserGrid.DataKeys[(int)e.Item.ItemIndex]);
        //cmd.Parameters.AddWithValue("@UserTypeId", SqlDbType.Int).Value = e.Item.Cells[0].Text;
        cmd.Parameters.AddWithValue("@EmailId", ((TextBox)e.Item.Cells[2].Controls[0]).Text);
        cmd.Parameters.AddWithValue("@MobileNumber", ((TextBox)e.Item.Cells[3].Controls[0]).Text);
        cmd.CommandText = "Update Users set EmailId=@EmailId,MobileNumber=@MobileNumber where UserId=" + UserId;
        cmd.Connection = con;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        UserGrid.EditItemIndex = -1;
        BindUserData();
    }
    #endregion
}
